﻿/*
Copyright (c) 2008 Ryan BB Schreiber, Brendan G Fry

Permission is hereby granted, free of charge, to any person obtaining a copy of 
this software and associated documentation files (the "Software"), to deal in 
the Software without restriction, including without limitation the rights to 
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies 
of the Software, and to permit persons to whom the Software is furnished to do 
so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all 
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A 
PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE 
OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Castle.ActiveRecord;
using log4net;
using NHibernate;
using NHibernate.Criterion;

namespace Darkside.GeneratorStudio.Library.Domain
{
    public partial class Table
    {
        private static readonly ILog _Log = LogManager.GetLogger(typeof(Table));
        IList<Column> _Columns;

        public IList<Column> Columns
        {
            
            get
            {
                using (new SessionScope(FlushAction.Never))
                {
                    if (_Columns == null)
                    {
                        _Columns = Column.FindAllByTable(this);
                        SetPrimaryAndCompositeKeys();
                        SetForeignAndCompositeKeys();
                    }
                }
                return (_Columns);
            }
        }

        private IList<ReferencedTableInfo> _ChildTables;
        public IList<ReferencedTableInfo> ChildTables
        {
            get
            {
                if (_ChildTables==null)
                {
                    _ChildTables = new List<ReferencedTableInfo>();
                    FetchChildTables();
                }
                return (_ChildTables);
            }
        }

        private IList<ReferencedTableInfo> _ParentTables;
        public IList<ReferencedTableInfo> ParentTables
        {
            get
            {
                if (_ParentTables == null)
                {
                    _ParentTables = new List<ReferencedTableInfo>();
                    FetchParentTables(); 
                }
                return (_ParentTables);
            }
        }

        private readonly TableSettings _Settings = new TableSettings();
        public TableSettings Settings
        {
            get
            {
                return (_Settings);
            }
        }

        public bool IsView
        {
            get { return (XType.Trim().ToUpper() == "V"); }
        }

        private void SetPrimaryAndCompositeKeys()
        {
            ISession session = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof (ActiveRecordBase));
            {
                var connection = session.Connection;

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                //TODO: Not elegant. At all. Really need to fix this up
                const string sqlQuery =
                    @"
                        SELECT 
                            tc.CONSTRAINT_NAME, tc.TABLE_NAME, ccu.COLUMN_NAME
		                FROM 
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
		                JOIN 
                            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON
			                ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			                AND ccu.TABLE_NAME = tc.TABLE_NAME
			                AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
		                WHERE 
                            tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
		                    AND tc.TABLE_NAME = @Table
                    ";

                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sqlQuery;
                    command.CommandType = CommandType.Text;

                    IDbDataParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "@Table";
                    parameter.Value = Name;

                    command.Parameters.Add(parameter);
                    command.Connection = connection;

                    var dataTable = new DataTable("PRIMARYKEYS");

                    dataTable.Load(command.ExecuteReader());

                    bool HasCompositeKeys = dataTable.Rows.Count > 1;

                    foreach (DataRow row in dataTable.Rows)
                    {
                        string PK_Column = row["COLUMN_NAME"].ToString() == String.Empty ? "" : row["COLUMN_NAME"].ToString();
                        foreach (var column in Columns)
                        {
                            if (column.Name.Equals(PK_Column))
                            {
                                column.IsPrimaryKey = true;
                                column.IsCompositePrimaryKeyMember = HasCompositeKeys;
                                break;
                            }
                        }
                    }
                }
            }
        }

        private void SetForeignAndCompositeKeys()
        {
            ISession session = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof(ActiveRecordBase));
            {
                var connection = session.Connection;

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                //TODO: Also Not elegant.
                const string sqlQuery = @"
                        SELECT 
                            tc.CONSTRAINT_NAME, ccu.COLUMN_NAME
		                FROM 
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
		                JOIN 
                            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON
			                ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			                AND ccu.TABLE_NAME = tc.TABLE_NAME
			                AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
		                WHERE 
                            tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
		                    AND tc.TABLE_NAME = @Table
                ";

                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sqlQuery;
                    command.CommandType = CommandType.Text;

                    IDbDataParameter parameter = command.CreateParameter();
                    parameter.ParameterName = "@Table";
                    parameter.Value = Name;

                    command.Parameters.Add(parameter);
                    command.Connection = connection;

                    var dataTable = new DataTable("FOREIGNKEYS");

                    dataTable.Load(command.ExecuteReader());

                    foreach (DataRow row in dataTable.Rows)
                    {
                        string columnName = row["COLUMN_NAME"].ToString() == String.Empty ? "" : row["COLUMN_NAME"].ToString();
                        foreach (var column in Columns)
                        {
                            if (column.Name.Equals(columnName))
                            {
                                column.IsForeignKey = true;
                                break;
                            }
                        }
                    }
                }
            }
        }


 
        private void FetchParentTables()
        {
            ISession session = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof (ActiveRecordBase));
            
            var connection = session.Connection;
            

            if (connection.State != ConnectionState.Open)
                connection.Open();

            ParentTables.Clear();
            try
            {

                IList<ForeignKey> foreignKeys = ActiveRecordBase<ForeignKey>.FindAll(new ICriterion[] {Restrictions.Eq("ForeignKeyId", Id)});

                foreach (var foreignKey in foreignKeys)
                {
                    var table = Find(foreignKey.ReferenceKeyId);

                    IDbCommand command = connection.CreateCommand();
                    command.CommandText = @"
                        SELECT     
                            sc1.name, sc1.colid, sc2.name AS ReferenceName, sc2.colid AS ReferenceColID 
                        FROM         
                            sysforeignkeys 
                                INNER JOIN
						            syscolumns AS sc1 ON sysforeignkeys.fkeyid = sc1.id AND sysforeignkeys.fkey = sc1.colid 
                                INNER JOIN
								    syscolumns AS sc2 ON sysforeignkeys.rkeyid = sc2.id AND sysforeignkeys.rkey = sc2.colid 
                                INNER JOIN 
                                    sysconstraints on sysconstraints.constid = sysforeignkeys.constid 
                        WHERE  
                            sc1.id = @objectid AND sc1.colid = @key AND sysconstraints.status <> 2115603";

                    command.CommandType = CommandType.Text;

                    IDbDataParameter parameter1 = command.CreateParameter();
                    parameter1.ParameterName = "@objectid";
                    parameter1.Value = foreignKey.ForeignKeyId;
                    command.Parameters.Add(parameter1);
                    IDbDataParameter parameter2 = command.CreateParameter();
                    parameter2.ParameterName = "@key";
                    parameter2.Value = foreignKey.ForeignKeyOrdinal;
                    command.Parameters.Add(parameter2);

                    var dataTable = new DataTable("REFERENCECOLS");

                    dataTable.Load(command.ExecuteReader());
                    if (dataTable.Rows.Count == 0)
                        continue;

                    var relatedTableInfo = new ReferencedTableInfo(this, table, dataTable.Rows[0]["name"].ToString(), dataTable.Rows[0]["ReferenceName"].ToString());

                    ParentTables.Add(relatedTableInfo);
                }
            }
            catch (SqlException sqlEx)
            {
                _Log.Error(sqlEx.Message, sqlEx);
                throw;
            }
        }

        private void FetchChildTables()
        {
            ISession session = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof(ActiveRecordBase));

            var connection = session.Connection;

            if (connection.State != ConnectionState.Open)
                connection.Open();


            ChildTables.Clear();
            try
            {

                IList<ForeignKey> foreignKeys = ActiveRecordBase<ForeignKey>.FindAll(new ICriterion[] {Restrictions.Eq("ReferenceKeyId", Id)});

                foreach (var foreignKey in foreignKeys)
                {

                    if (ActiveRecordBase<Constraint>.Find(foreignKey.ConstraintId).Status == 2115603)
                        continue;

                    var table = Find(foreignKey.ForeignKeyId);

                    IDbCommand command = connection.CreateCommand();
                    command.CommandText =
                        @"
                        SELECT 
                            DISTINCT sc1.name, sc1.colid, sc2.name AS ReferenceName, sc2.colid AS ReferenceColID
                        FROM 
                            sysforeignkeys 
                                INNER JOIN
                                    syscolumns AS sc1 ON sysforeignkeys.fkeyid = sc1.id AND sysforeignkeys.fkey = sc1.colid 
                                INNER JOIN
                                    syscolumns AS sc2 ON sysforeignkeys.rkeyid = sc2.id AND sysforeignkeys.rkey = sc2.colid 
								INNER JOIN 
                                    sysconstraints on sysconstraints.constid = sysforeignkeys.constid 
                        WHERE
                            sc2.id = @objectid AND sc1.colid = @key AND sysconstraints.status <> 2069
                        AND 
                            sysforeignkeys.fkeyid=@fkeyid";

                    command.CommandType = CommandType.Text;

                    IDbDataParameter parameter1 = command.CreateParameter();
                    parameter1.ParameterName = "@objectid";
                    parameter1.Value = foreignKey.ReferenceKeyId;
                    command.Parameters.Add(parameter1);
                    IDbDataParameter parameter2 = command.CreateParameter();
                    parameter2.ParameterName = "@key";
                    parameter2.Value = foreignKey.ForeignKeyOrdinal;
                    command.Parameters.Add(parameter2);
                    IDbDataParameter parameter3 = command.CreateParameter();
                    parameter3.ParameterName = "@fkeyid";
                    parameter3.Value = foreignKey.ForeignKeyId;
                    command.Parameters.Add(parameter3);

                    var dataTable = new DataTable("REFERENCECOLS");

                    dataTable.Load(command.ExecuteReader());
                    if (dataTable.Rows.Count == 0)
                        continue;

                    //var relatedTableInfo = new ReferencedTableInfo(this, table, dataTable.Rows[0]["name"].ToString(), dataTable.Rows[0]["ReferenceName"].ToString());
                    var relatedTableInfo = new ReferencedTableInfo(this, table, dataTable.Rows[0]["ReferenceName"].ToString(), dataTable.Rows[0]["name"].ToString());

                    ChildTables.Add(relatedTableInfo);
                }
            }
            catch (SqlException sqlEx)
            {
                _Log.Error(sqlEx.Message, sqlEx);
                throw;
            }
        }

    }
}
